import datetime
import numpy as py 
import pymysql
import pandas as pd
import pymysql.cursors
from sklearn.preprocessing import MinMaxScaler
from joblib import dump
import requests 
 
class WeatherUtils(object):
    """天气类
    
    """
    def __init__(self):
        self.date_list = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
 
        self.url = 'http://v1.yiketianqi.com/api'
        
    def get_data(self):
        """获取天气数据
        
        """    
        date_list = []
        for d in self.date_list:
            conf = {'appid':'79719859',
                    'appsecret'	:'ximzbY7a',
                    'version':'history',
                    'year'	:d[:4],
                    'month'	:d[5:7],	
                    'city':'南昌'
                
            }
            res = requests.get(self.url+'?',params=conf)
            res_data = res.json()
            print(res_data)
            for i in res_data['data']:
                date_list.append({
                    'date': datetime.datetime.strptime(i['ymd'],'%Y-%m-%d'),
                    'bWendu':i['bWendu'],
                    'yWendu':i['yWendu'],
                    'tianqi':i['tianqi'],
                    'fengli':i['fengli'],
                })
                
            df =pd.DataFrame(date_list)
            df.to_csv('D:/lyj/data-mining/xm7/weather.csv')
            
class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='test1',
            port=3306,
            charset='utf8'
        )
        self.weather_data=pd.read_csv('D:/lyj/data-mining/xm7/weather.csv')
        
    def is_holiday(self,date):
        """判断是否为节假日"""
        if date in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2024-01-02', '2024-01-03']:
            return 1
        return 0
        
    def get_data(self):
            cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
            sql = """
            SELECT DATE(g.create_time) as date, count(*) as count FROM order_user_gate_rel g WHERE DATE(g.create_time) < '2025-01-01' GROUP BY date
            """
            cursor.execute(sql)
            ret = cursor.fetchall()
            df = pd.DataFrame(ret)
            
            # 合并天气数据
            self.weather_data['date'] = pd.to_datetime(self.weather_data['date'])
            df['date'] = pd.to_datetime(df['date'])
            df_pivot = pd.merge(self.weather_data,df,on='date')
            print(df_pivot.head)
            df_pivot.set_index('date',inplace = True)
            
            df_pivot['dow'] = df_pivot.index.dayofweek
            df_pivot['month'] = df_pivot.index.month
            df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
            
            df_pivot = pd.get_dummies(df_pivot, columns=['dow','month','tianqi','fengli'], dtype=int)
            
            scaler = MinMaxScaler()
            df[['count']]= scaler.fit_transform(df[['count']])
            dump(scaler,"./xm7/scaler.joblib")
            
            df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('°','').astype(int)
            df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('°','').astype(int)

            weather_features = df_pivot[['bWendu','yWendu']]
            df_pivot[['bWendu','yWendu']] = scaler.fit_transform(weather_features)
            dump(scaler,'./xm7/weather_scaler.joblib')
            print(df_pivot.head)
            
            df_pivot.to_csv('./xm7/scenic_data.csv',index=False)
            
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()
    # we=WeatherUtils()
    # we.get_data()
    